/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models;

import java.util.Calendar;
import java.sql.*;
import java.util.Date;

import java.text.*;
import java.util.Vector;

import Beans.NgonNgu;

/**
 *
 * @author Venus
 */
public class MonAn_Model {

    public static int TaoMa() {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select max(MaMA) from monan";
            rss = stmt.executeQuery(query);
            int maht = 0;// biến maht chứa mã của khu vực hiện tại
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
            return maht + 1;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static Vector LayDSMA(int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaMA), MaNN, TenMA, TenMAVT, MaNMA, MoTa, CoPhucVu from monan where MaNN = " + langid + " Order by MaMA desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static Vector LayDSMATheoNMA(int langid, int MaNMA) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaMA), MaNN, TenMA, TenMAVT, MaNMA, MoTa, CoPhucVu from monan where MaNN = " + langid + " and MaNMA = " + MaNMA + " and CoPhucVu = 1 Order by MaMA desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean AddMonAn(int MaMA, String TenMA, String TenMAVT, String MaNMA, String MoTa) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            NgonNgu[] dsnn = NgonNgu_Model.DSNgonNgu();
            String x = "insert into monan values ";
            for (int i = 0; i < dsnn.length; i++) {
                x = x + "(" + MaMA + ", " + dsnn[i].getMaNN() + ", '" + TenMA + "' , '" + TenMAVT + "', " + MaNMA + ", '" + MoTa + "', 1 ),";
            }
            String query = x.substring(0, x.length() - 1);
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    
    public static boolean AddHinhAnhMonAn(int MaMA, String TenHA) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " insert into hinhanhma(MaMA, TenHA) values (" + MaMA + ", '" + TenHA + "') ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static String LayTenHinhAnh(int mama) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        String maht = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TenHA from hinhanhma where  MaMA = " + mama;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public static String LayTenMA(int mama, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        String maht = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TenMA from monan where  MaMA = " + mama + " and MaNN = " + langid + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public static String LayTenNMA(int mama, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        String maht = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(TenNMA) from nhommonan A, monan B where A.MaNMA = B.MaNMA and  B.MaMA = " + mama + " and A.MaNN = " + langid + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public static boolean AddDVTMA(int mama, int madvt) {
        Connection conn = null;

        Statement stmt = null;
        String query = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            query = " Insert into dvtmonan values (" + mama + ", " + madvt + ") ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }

    }

    public static boolean DeleteDVTMA(int mama) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " Delete from dvtmonan where MaMA = " + mama + " ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static int LayMaDVT(String TenDVT, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        int maht = 0;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select MaDVT from donvitinh where  TenDVT = '" + TenDVT + "' and MaNN = " + langid + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public static String LayMaTTe(int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        String maht = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select MaTTe from tiente where  MaNN = " + langid + " and MacDinh = 1 ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public static boolean AddGia(int mama, int MaDVT, String MaTTe, String gia) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "";
            if (!gia.equals("0")) {
                query = " Insert into gia values (" + mama + ", " + MaDVT + ", '" + MaTTe + "', " + gia + ") ";
            }
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean AddLichSuGia(int mama, int MaDVT, String gia) {
        Connection conn = null;

        Statement stmt = null;
        String query = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            Calendar cal = Calendar.getInstance();
            SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
            String ngaygio = fm.format(cal.getTime());
            query = " Insert into lichsugia(NgayGio, MaMA, MaDVT, Gia) values ('" + ngaygio + "', " + mama + ", " + MaDVT + ",  " + gia + ") ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }

    }

    public static boolean DeleteGia(int mama) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " Delete from gia where MaMA = " + mama + " ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static float LayGia(int MaMA) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        float maht = 0;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select Gia from gia where MaMA = " + MaMA + " limit 1  ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getFloat(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }

    public static boolean UpdateStatusMA(String mama, String status) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update monan set CoPhucVu = " + status + " where MaMA = " + mama;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean UpdateMA(int MaMA, String TenMA, String TenMAVT, String MaNMA, String MoTa, int langid) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update monan set TenMA = '" + TenMA + "', TenMAVT = '"+TenMAVT+"', MaNMA = "+MaNMA+", MoTa = '"+MoTa+"' where MaMA = " + MaMA+" and MaNN = "+langid+"";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

     public static boolean UpdateHinhAnhMonAn(int mama, String tenha) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update hinhanhma set TenHA = '" + tenha + "' where MaMA = " + mama;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean UpdateStatusMAFolowNMA(String manma, String status) {
        Connection conn = null;

        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update monan set CoPhucVu = " + status + " where MaNMA = " + manma;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector SearchMA(String MaNMA, String TENMA, String TENVT, String GIA, String CoPhucVu, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(A.MaMA), A.MaNN, TenMA, TenMAVT, A.MaNMA, MoTa, CoPhucVu "
                    + " from monan A "
                    + " left join gia C on A.MaMA = C.MaMA "
                    + " where A.MaNN = " + langid + " ";
            String[] arr = {MaNMA, TENMA, TENVT, GIA, CoPhucVu};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(GIA)) {
                    query = query + " and gia = " + GIA + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(CoPhucVu)) {
                    query = query + " and CoPhucVu = " + CoPhucVu + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(MaNMA)) {
                    query = query + " and MaNMA = " + MaNMA + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENMA)) {
                    query = query + " and TenMA like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENVT)) {
                    query = query + " and TenMAVT like '%" + arr[i] + "%' ";
                }
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SearchMAKM(String MaNMA, String TENMA, String ngaythu, String GIA, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(A.MaMA), A.MaNN, TenMA, TenMAVT, A.MaNMA, MoTa, CoPhucVu "
                    + " from monan A"
                    + " inner join ngaythu  "
                    + " left join gia C on A.MaMA = C.MaMA "
                    + " where A.MaNN = " + langid + " and A.MaNMA = " + MaNMA + " ";
            String[] arr = {TENMA, ngaythu, GIA};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(GIA)) {
                    query = query + " and gia = " + GIA + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENMA)) {
                    query = query + " and TenMA like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(ngaythu)) {
                    query = query + " and MaNgay = " + arr[i] + " ";
                }
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTenMAKM(String MaNMA, String TENMA, String ngaythu, String GIA, int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(A.MaMA), A.MaNN, TenMA, TenMAVT, A.MaNMA, MoTa, CoPhucVu "
                    + " from monan A"
                    + " inner join ngaythu  "
                    + " left join gia C on A.MaMA = C.MaMA "
                    + " where A.MaNN = " + langid + " and A.MaNMA = " + MaNMA + " ";
            String[] arr = {TENMA, ngaythu, GIA};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(GIA)) {
                    query = query + " and gia = " + GIA + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENMA)) {
                    query = query + " and TenMA like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(ngaythu)) {
                    query = query + " and MaNgay = " + arr[i] + " ";
                }
            }
            query = query + " Order by TenMA " + order + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchNgayThuKM(String MaNMA, String TENMA, String ngaythu, String GIA, int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(A.MaMA), A.MaNN, TenMA, TenMAVT, A.MaNMA, MoTa, CoPhucVu "
                    + " from monan A"
                    + " inner join ngaythu  "
                    + " left join gia C on A.MaMA = C.MaMA "
                    + " where A.MaNN = " + langid + " and A.MaNMA = " + MaNMA + " ";
            String[] arr = {TENMA, ngaythu, GIA};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(GIA)) {
                    query = query + " and gia = " + GIA + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENMA)) {
                    query = query + " and TenMA like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(ngaythu)) {
                    query = query + " and MaNgay = " + arr[i] + " ";
                }
            }
            query = query + " Order by TenNgay " + order + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchGiaKM(String MaNMA, String TENMA, String ngaythu, String GIA, int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(A.MaMA), A.MaNN, TenMA, TenMAVT, A.MaNMA, MoTa, CoPhucVu "
                    + " from monan A"
                    + " inner join ngaythu  "
                    + " left join gia C on A.MaMA = C.MaMA "
                    + " where A.MaNN = " + langid + " and A.MaNMA = " + MaNMA + " ";
            String[] arr = {TENMA, ngaythu, GIA};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(GIA)) {
                    query = query + " and gia = " + GIA + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENMA)) {
                    query = query + " and TenMA like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(ngaythu)) {
                    query = query + " and MaNgay = " + arr[i] + " ";
                }
            }
            query = query + " Order by Gia " + order + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTenNMA(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(A.MaMA), A.MaNN, TenMA, TenMAVT, A.MaNMA, MoTa, CoPhucVu from monan A, nhommonan B  "
                    + " where A.MaNMA = B.MaNMA and  A.MaNN = " + langid + " Order by TenNMA  " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTenMA(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaMA), MaNN, TenMA, TenMAVT, MaNMA, MoTa, CoPhucVu from monan   "
                    + " where   MaNN = " + langid + " Order by TenMA  " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTenMAKM(int langid, String order, String manma) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaMA), MaNN, TenMA, TenMAVT, MaNMA, MoTa, CoPhucVu from monan   "
                    + " where MaNN = " + langid + " and MaNMA = " + manma + " Order by TenMA  " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortNgayThuKM(int langid, String order, String manma) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaMA), A.MaNN, TenMA, TenMAVT, MaNMA, MoTa, CoPhucVu "
                    + " from monan A, ngaythu B   "
                    + " where A.MaNN = " + langid + " and MaNMA = " + manma + " Order by TenNgay  " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTenVT(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaMA), MaNN, TenMA, TenMAVT, MaNMA, MoTa, CoPhucVu from monan   "
                    + " where   MaNN = " + langid + " Order by TenMAVT  " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortGiaMA(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(A.MaMA), A.MaNN, TenMA, TenMAVT, A.MaNMA, MoTa, CoPhucVu "
                    + " from monan A left join gia B on A.MaMA = B.MaMA  "
                    + " where  A.MaNN = " + langid + " Order by Gia  " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortGiaKM(int langid, String order, String manma) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(A.MaMA), A.MaNN, TenMA, TenMAVT, A.MaNMA, MoTa, CoPhucVu "
                    + " from monan A left join gia B on A.MaMA = B.MaMA  "
                    + " where  A.MaNN = " + langid + " and A.MaNMA = " + manma + " Order by Gia  " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTTMA(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaMA), MaNN, TenMA, TenMAVT, MaNMA, MoTa, CoPhucVu from monan   "
                    + " where   MaNN = " + langid + " Order by CoPhucVu  " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTenNMA(String MaNMA, String TENMA, String TENVT, String GIA, String CoPhucVu, String order, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(A.MaMA), A.MaNN, TenMA, TenMAVT, A.MaNMA, MoTa, CoPhucVu "
                    + " from monan A "
                    + " inner join nhommonan B on A.MaNMA = B.MaNMA "
                    + " left join gia C on A.MaMA = C.MaMA   "
                    + " where A.MaNN = " + langid + " ";
            String[] arr = {MaNMA, TENMA, TENVT, GIA, CoPhucVu};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(GIA)) {
                    query = query + " and gia = " + GIA + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(CoPhucVu)) {
                    query = query + " and CoPhucVu = " + CoPhucVu + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(MaNMA)) {
                    query = query + " and A.MaNMA = " + MaNMA + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENMA)) {
                    query = query + " and TenMA like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENVT)) {
                    query = query + " and TenMAVT like '%" + arr[i] + "%' ";
                }
            }
            query = query + " Order by TenNMA " + order + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTenMA(String MaNMA, String TENMA, String TENVT, String GIA, String CoPhucVu, String order, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(A.MaMA), A.MaNN, TenMA, TenMAVT, A.MaNMA, MoTa, CoPhucVu "
                    + " from monan A "
                    + " inner join nhommonan B on A.MaNMA = B.MaNMA "
                    + " left join gia C on A.MaMA = C.MaMA   "
                    + " where A.MaNN = " + langid + " ";
            String[] arr = {MaNMA, TENMA, TENVT, GIA, CoPhucVu};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(GIA)) {
                    query = query + " and gia = " + GIA + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(CoPhucVu)) {
                    query = query + " and CoPhucVu = " + CoPhucVu + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(MaNMA)) {
                    query = query + " and A.MaNMA = " + MaNMA + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENMA)) {
                    query = query + " and TenMA like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENVT)) {
                    query = query + " and TenMAVT like '%" + arr[i] + "%' ";
                }
            }
            query = query + " Order by TenMA " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTenVT(String MaNMA, String TENMA, String TENVT, String GIA, String CoPhucVu, String order, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(A.MaMA), A.MaNN, TenMA, TenMAVT, A.MaNMA, MoTa, CoPhucVu "
                    + " from monan A "
                    + " inner join nhommonan B on A.MaNMA = B.MaNMA "
                    + " left join gia C on A.MaMA = C.MaMA   "
                    + " where A.MaNN = " + langid + " ";
            String[] arr = {MaNMA, TENMA, TENVT, GIA, CoPhucVu};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(GIA)) {
                    query = query + " and gia = " + GIA + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(CoPhucVu)) {
                    query = query + " and CoPhucVu = " + CoPhucVu + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(MaNMA)) {
                    query = query + " and A.MaNMA = " + MaNMA + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENMA)) {
                    query = query + " and TenMA like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENVT)) {
                    query = query + " and TenMAVT like '%" + arr[i] + "%' ";
                }
            }
            query = query + " Order by TenMAVT " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchGiaMA(String MaNMA, String TENMA, String TENVT, String GIA, String CoPhucVu, String order, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(A.MaMA), A.MaNN, TenMA, TenMAVT, A.MaNMA, MoTa, CoPhucVu "
                    + " from monan A "
                    + " inner join nhommonan B on A.MaNMA = B.MaNMA "
                    + " left join gia C on A.MaMA = C.MaMA   "
                    + " where A.MaNN = " + langid + " ";
            String[] arr = {MaNMA, TENMA, TENVT, GIA, CoPhucVu};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(GIA)) {
                    query = query + " and gia = " + GIA + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(CoPhucVu)) {
                    query = query + " and CoPhucVu = " + CoPhucVu + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(MaNMA)) {
                    query = query + " and A.MaNMA = " + MaNMA + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENMA)) {
                    query = query + " and TenMA like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENVT)) {
                    query = query + " and TenMAVT like '%" + arr[i] + "%' ";
                }
            }
            query = query + " Order by Gia " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTTMA(String MaNMA, String TENMA, String TENVT, String GIA, String CoPhucVu, String order, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(A.MaMA), A.MaNN, TenMA, TenMAVT, A.MaNMA, MoTa, CoPhucVu "
                    + " from monan A "
                    + " inner join nhommonan B on A.MaNMA = B.MaNMA "
                    + " left join gia C on A.MaMA = C.MaMA   "
                    + " where A.MaNN = " + langid + " ";
            String[] arr = {MaNMA, TENMA, TENVT, GIA, CoPhucVu};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(GIA)) {
                    query = query + " and gia = " + GIA + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(CoPhucVu)) {
                    query = query + " and CoPhucVu = " + CoPhucVu + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(MaNMA)) {
                    query = query + " and A.MaNMA = " + MaNMA + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENMA)) {
                    query = query + " and TenMA like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(TENVT)) {
                    query = query + " and TenMAVT like '%" + arr[i] + "%' ";
                }
            }
            query = query + " Order by CoPhucVu " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean CheckDVT(String tendvt, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from donvitinh where MaNN = " + langid + " and TenDVT = '" + tendvt + "' ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean CheckDVTMA(int MaDVT, int MaMA) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from dvtmonan where MaDVT = " + MaDVT + " and MaMA = " + MaMA + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean CheckTienTe(int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from tiente where MaNN = " + langid + "  and MacDinh = 1 ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector LangMA(int mama) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from monan where MaMA = " + mama;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector Detail(String mama, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from monan where MaMA = " + mama + " and MaNN = " + langid + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector LayGia(String mama, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from gia where MaMA = " + mama + "  ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.Gia g = new Beans.Gia();
                g.setMaMA(rss.getInt(1));
                g.setMaDVT(rss.getInt(2));
                g.setMaTTe(rss.getString(3));
                g.setGia(rss.getFloat(4));
                v.add(g);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean EditTenMA(String mama, String mann, String tenma) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update monan set TenMA = '" + tenma + "' where MaMA = " + mama + " and MaNN = " + mann;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static float LayRsGia(int mama, int discount) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        float gia = 0;
        float rsgia = 0;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select Gia from gia where  MaMA = " + mama + " limit 1";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                gia = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        if (discount == 0) {
            rsgia = gia;
        } else {
            rsgia = gia - ((gia * discount) / 100);
        }

        return rsgia;
    }

    public static boolean ThemTuyChonMA(int matc, String mama) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " Insert into tuychonmonan values (" + matc + ", " + mama + ") ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static Vector LayTuyChonMA(String mama) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select * from tuychonmonan where MaMA = " + mama + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TuyChonMA tcma = new Beans.TuyChonMA();
                tcma.setMaTC(rss.getInt(1));
                tcma.setMaMA(rss.getInt(2));
                v.add(tcma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean XoaTuyChonMA(String matc, String mama) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " Delete from tuychonmonan where MaTC = " + matc + " and MaMA = " + mama + " ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean CheckLichSuGia(int MaMA, int MaDVT, String gia) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from lichsugia where MaMA = " + MaMA + " and MaDVT = " + MaDVT + " and Gia = " + gia + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static Vector LoadMA(int langid, String tenma) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        String query = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            query = "select * from monan where mann = 2 and cophucvu = 1 and ( tenma like '%" + tenma + "%' or tenmavt like '%" + tenma + "%' ) order by mama";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.MonAn ma = new Beans.MonAn();
                ma.setMaMA(rss.getInt(1));
                ma.setMaNN(rss.getInt(2));
                ma.setTenMA(rss.getString(3));
                ma.setTenMAVT(rss.getString(4));
                ma.setMaNMA(rss.getInt(5));
                ma.setMoTa(rss.getString(6));
                ma.setCoPhucVu(rss.getByte(7));
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static int LoadGia(String ma, String dvt) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        int gia = 0;
        int giamgia = 0;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            Calendar now = Calendar.getInstance();
            String ngay = String.valueOf(now.get(Calendar.DAY_OF_WEEK));
            SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
            String ngaygio = fm.format(now.getTime());
            Calendar c1 = Calendar.getInstance();
            Calendar cbd = Calendar.getInstance();
            Calendar ckt = Calendar.getInstance();
            Date date1 = fm.parse(ngaygio);
            c1.setTime(date1);
            String query1 = " Select distinct(MaKM), NgayBD, NgayKT from khuyenmai where MaLKM = 1 and TinhTrang = 1 ";
            rss = stmt.executeQuery(query1);
            String ngaybatdau = "";
            String ngayketthuc = "";
            int makhuyenmai = 0;
            while (rss.next()) {
                int MaKM = rss.getInt(1);
                String y = rss.getString(2);
                String x = rss.getString(3);
                Date date2 = fm.parse(y);
                Date date3 = fm.parse(x);
                cbd.setTime(date2);
                ckt.setTime(date3);
                if (c1.before(ckt) && c1.after(cbd)) {
                    makhuyenmai = MaKM;
                    ngaybatdau = y;
                    ngayketthuc = x;
                }
                if (c1.equals(ckt) || c1.equals(cbd)) {
                    makhuyenmai = MaKM;
                    ngaybatdau = y;
                    ngayketthuc = x;
                }
            }
            if (!ngaybatdau.equals("") && !ngayketthuc.equals("")) {
                String query = "select max(giamgia) from kmtheongay N,khuyenmai K where K.MaKM = N.MaKM and K.MaKM = " + makhuyenmai + " and MaMA = " + ma + " and ( MaNgay = 0 or MaNgay = " + ngay + " )";
                rss = stmt.executeQuery(query);
                while (rss.next()) {
                    giamgia = rss.getInt(1);
                }
            }
            String query = "select gia from gia where mama =" + ma + " and madvt = " + dvt;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                gia = rss.getInt(1);
            }
            gia = gia - giamgia * gia / 100;
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return gia;
    }
    public static int LoadGiamGia(String ma) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        int giamgia = 0;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            Calendar now = Calendar.getInstance();
            String ngay = String.valueOf(now.get(Calendar.DAY_OF_WEEK));
            SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yyyy");
            String ngaygio = fm.format(now.getTime());
            Calendar c1 = Calendar.getInstance();
            Calendar cbd = Calendar.getInstance();
            Calendar ckt = Calendar.getInstance();
            Date date1 = fm.parse(ngaygio);
            c1.setTime(date1);
            String query1 = " Select distinct(MaKM), NgayBD, NgayKT from khuyenmai where MaLKM = 1 and TinhTrang = 1 ";
            rss = stmt.executeQuery(query1);
            String ngaybatdau = "";
            String ngayketthuc = "";
            int makhuyenmai = 0;
            while (rss.next()) {
                int MaKM = rss.getInt(1);
                String y = rss.getString(2);
                String x = rss.getString(3);
                Date date2 = fm.parse(y);
                Date date3 = fm.parse(x);
                cbd.setTime(date2);
                ckt.setTime(date3);
                if (c1.before(ckt) && c1.after(cbd)) {
                    makhuyenmai = MaKM;
                    ngaybatdau = y;
                    ngayketthuc = x;
                }
                if (c1.equals(ckt) || c1.equals(cbd)) {
                    makhuyenmai = MaKM;
                    ngaybatdau = y;
                    ngayketthuc = x;
                }
            }
            if (!ngaybatdau.equals("") && !ngayketthuc.equals("")) {
                String query = "select max(giamgia) from kmtheongay N,khuyenmai K where K.MaKM = N.MaKM and K.MaKM = " + makhuyenmai + " and MaMA = " + ma + " and ( MaNgay = 0 or MaNgay = " + ngay + " )";
                rss = stmt.executeQuery(query);
                while (rss.next()) {
                    giamgia = rss.getInt(1);
                }
            }

            stmt.close();
            rss.close();
            conn.close();
            return giamgia;
        } catch (Exception e) {
            return giamgia;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }

    }
     public static Vector LoadMonAn(int langid,String nhom,String loai, String tenma,String start) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        String query = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            query = "select MA.mama, tenma, tendvt, tenha, mota,DVM.madvt " +
                    "from monan MA, dvtmonan DVM, hinhanhma HA, donvitinh DVT, nhommonan NMA, loaimonan LMA" +
                    " where DVT.mann = "+langid+" and MA.mann = " +langid +" and  NMA.MaNN ="+langid+"  and LMA.MaNN = "+langid+" and "+
                    "MA.mama = DVM.mama and DVT.madvt = DVM.madvt and MA.MaNMA = NMA.MaNMA and NMA.MaLMA = LMA.MaLMA and HA.MaMA = MA.MaMA and cophucvu = 1 " +
                    "and ( tenma like '%" + tenma + "%' or tenmavt like '%" + tenma + "%' ) " ;

            if(!nhom.equals(""))
                query = query + " and NMA.manma = "+ nhom;
            if(!loai.equals(""))
                query = query + " and LMA.malma = "+ loai;
            query = query +  " order by tenmavt limit "+start + ",4";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                int mma = rss.getInt(1);
                String mama = String.valueOf(mma);
                int mdvt = rss.getInt(6);
                String dvt = String.valueOf(mdvt);
                int gia = LoadGia(mama, dvt);
                String[] ma = new String[7];
                ma[0]= mama;
                ma[1]= rss.getString(2);
                ma[2]= rss.getString(6);
                ma[3]= rss.getString(3);
                ma[4]= rss.getString(4);
                ma[5]= rss.getString(5);
                ma[6]= String.valueOf(gia);
                v.add(ma);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
     }
      public static int SoLuongMA(int langid,String loai,String nhom,String tenma) {
            Connection conn = null;
            ResultSet rss = null;
            Statement stmt = null;
            String query = "";
            int soluong = 0;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            query = "select count(MA.mama)" +
                    "from monan MA, dvtmonan DVM, hinhanhma HA, donvitinh DVT, nhommonan NMA, loaimonan LMA" +
                    " where DVT.mann = "+langid+" and MA.mann = " +langid +" and  NMA.MaNN ="+langid+"  and LMA.MaNN = "+langid+" and "+
                    "MA.mama = DVM.mama and DVT.madvt = DVM.madvt and MA.MaNMA = NMA.MaNMA and NMA.MaLMA = LMA.MaLMA and HA.MaMA = MA.MaMA and cophucvu = 1 " +
                    "and ( tenma like '%" + tenma + "%' or tenmavt like '%" + tenma + "%' ) " ;

            if(!nhom.equals(""))
                query = query + " and NMA.manma = "+ nhom;
            if(!loai.equals(""))
                query = query + " and LMA.malma = "+ loai;
            rss = stmt.executeQuery(query);
            
            while (rss.next()) {
                soluong = rss.getInt(1);
            }
        }
        catch (Exception e) {
            e.getMessage();

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
       return soluong;
      }
}
